package com.leosoft.heatmonitor.dao;

import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.hibernate.Criteria;
import org.hibernate.criterion.ProjectionList;
import org.hibernate.criterion.Projections;
import org.hibernate.criterion.Restrictions;
import org.springframework.stereotype.Component;

import com.leosoft.heatmonitor.model.Data;

@Component
public class DataDAO extends BaseDAO {
	SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

	public Map<String, Object> list(int startIndex, int length, List<String[]> wherePart, List<String[]> odrerPart,
			String startTime, String endTime, String userId) {

		StringBuilder fromBuilder = new StringBuilder(" FROM data d");
		if (!userId.isEmpty()) {
			fromBuilder.append(",control c,project p");
		}
		StringBuilder whereBuilder = new StringBuilder();
		if (!userId.isEmpty()) {
			whereBuilder.append("where c.project_id = p.id AND p.id = d.projectId and c.user_id='").append(userId)
					.append("'");
		}
		StringBuilder countTotalQuery = new StringBuilder("SELECT count(d.id)");

		Object totalCount = this.getSession()
				.createSQLQuery(countTotalQuery.append(fromBuilder).append(whereBuilder).toString()).uniqueResult();

		for (String[] part : wherePart) {
			whereBuilder.append(" and d.").append(part[0]).append(" like '").append(part[1]).append("%'");
		}
		if (!startTime.isEmpty()) {
			whereBuilder.append(" and d.createTime >='").append(startTime).append(" 00:00:00'");
		}
		if (!endTime.isEmpty()) {
			whereBuilder.append(" and d.createTime <='").append(endTime).append(" 23:59:59'");
		}
		if (whereBuilder.length() != 0 && !whereBuilder.toString().startsWith(" WHERE ")) {
			whereBuilder = whereBuilder.delete(0, 4);
			whereBuilder.insert(0, " WHERE ");
		}

		StringBuilder countFilteredQuery = new StringBuilder("SELECT count(d.id) ");

		Object recordCount = this.getSession()
				.createSQLQuery(countFilteredQuery.append(fromBuilder).append(whereBuilder).toString()).uniqueResult();

		for (String[] orders : odrerPart) {
			whereBuilder.append(" order by d.").append(orders[0]).append(" ").append(orders[1]);
		}
		whereBuilder.append(" limit ").append(startIndex).append(",").append(length);

		StringBuilder dataQuery = new StringBuilder(
				"SELECT d.projectId, d.deviceKey, `user`, door, building, d.targetKey, currentHeat, totalHeat, area, currentPeriod, totalPeriod, realTemperature, avgTemperature, userTemperature, createTime, projectName");

		List<Object[]> datas = this.getSession()
				.createSQLQuery(dataQuery.append(fromBuilder).append(whereBuilder).toString()).list();
		List<Map<String, Object>> resultList = new ArrayList<>();
		for (Object[] data : datas) {
			synchronized (format) {
				String createTime = format.format(data[14]);
				Map<String, Object> result = new HashMap<>();
				result.put("projectId", data[0]);
				result.put("projectName", data[15]);
				result.put("deviceKey", data[1]);
				result.put("area", data[8]);
				result.put("targetKey", data[5]);
				result.put("building", data[4]);
				result.put("door", data[3]);
				result.put("user", data[2]);
				result.put("avgTemperature", data[12]);
				result.put("createtime", createTime);
				result.put("currentHeat", data[6]);
				result.put("currentPeriod", data[9]);
				result.put("realTemperature", data[11]);
				result.put("totalHeat", data[7]);
				result.put("totalPeriod", data[10]);
				result.put("userTemperature", data[13]);
				resultList.add(result);
			}

		}
		Map<String, Object> result = new HashMap<String, Object>();
		result.put("recordsTotal", totalCount);
		result.put("recordsFiltered", recordCount);
		result.put("data", resultList);
		return result;
	}

	public Map<String, Object> monitorList(int startIndex, int length, List<String[]> wherePart,
			List<String[]> odrerPart, String startTime, String endTime, String userId) {
		StringBuilder fromBuilder = new StringBuilder(
				" FROM ( SELECT projectId, deviceKey, targetKey, max(createTime) as time FROM DATA GROUP BY projectId, deviceKey, targetKey ) d1, DATA d, project p");

		if (!userId.isEmpty()) {
			fromBuilder.append(",control c");
		}
		StringBuilder whereBuilder = new StringBuilder(
				" WHERE d.projectId = d1.projectId AND d.deviceKey = d1.deviceKey AND d.targetKey = d1.targetKey AND d.createTime = d1.time AND d.projectId = p.id");
		if (!userId.isEmpty()) {
			whereBuilder.append("and c.project_id = p.id and c.user_id='").append(userId).append("'");
		}

		StringBuilder countTotalQuery = new StringBuilder("SELECT count(d.id)");
		Object totalCount = this.getSession()
				.createSQLQuery(countTotalQuery.append(fromBuilder).append(whereBuilder).toString()).uniqueResult();

		for (String[] wheres : wherePart) {
			whereBuilder.append(" and d.").append(wheres[0]).append(" like '").append(wheres[1]).append("%'");
		}
		if (!startTime.isEmpty()) {
			whereBuilder.append(" and d.createTime >='").append(startTime).append(" 00:00:00'");
		}
		if (!endTime.isEmpty()) {
			whereBuilder.append(" and d.createTime <='").append(endTime).append(" 23:59:59'");
		}
		if (whereBuilder.length() != 0 && !whereBuilder.toString().startsWith(" WHERE ")) {
			whereBuilder = whereBuilder.delete(0, 4);
			whereBuilder.insert(0, " WHERE ");
		}
		StringBuilder countFilteredQuery = new StringBuilder("SELECT count(d.id)");

		Object recordCount = this.getSession()
				.createSQLQuery(countFilteredQuery.append(fromBuilder).append(whereBuilder).toString()).uniqueResult();

		for (String[] orders : odrerPart) {
			whereBuilder.append(" order by d.").append(orders[0]).append(" ").append(orders[1]);
		}
		whereBuilder.append(" limit ").append(startIndex).append(",").append(length);

		StringBuilder dataQuery = new StringBuilder(
				"SELECT d.projectId, d.deviceKey, `user`, door, building, d.targetKey, currentHeat, totalHeat, area, currentPeriod, totalPeriod, realTemperature, avgTemperature, userTemperature, createTime, projectName, p.rate * totalHeat AS fee");

		List<Object[]> datas = this.getSession()
				.createSQLQuery(dataQuery.append(fromBuilder).append(whereBuilder).toString()).list();
		List<Map<String, Object>> resultList = new ArrayList<>();
		for (Object[] data : datas) {
			synchronized (format) {
				String createTime = format.format(data[14]);
				Map<String, Object> result = new HashMap<>();
				result.put("projectId", data[0]);
				result.put("projectName", data[15]);
				result.put("deviceKey", data[1]);
				result.put("area", data[8]);
				result.put("targetKey", data[5]);
				result.put("building", data[4]);
				result.put("door", data[3]);
				result.put("user", data[2]);
				result.put("avgTemperature", data[12]);
				result.put("createtime", createTime);
				result.put("currentHeat", data[6]);
				result.put("currentPeriod", data[9]);
				result.put("realTemperature", data[11]);
				result.put("totalHeat", data[7]);
				result.put("totalPeriod", data[10]);
				result.put("userTemperature", data[13]);
				result.put("fee", data[16]);
				resultList.add(result);
			}

		}
		Map<String, Object> result = new HashMap<String, Object>();
		result.put("recordsTotal", totalCount);
		result.put("recordsFiltered", recordCount);
		result.put("data", resultList);
		return result;

	}

	public List<Map<String, String>> trends(String projectId, String start, String end, String userId, String dataName) {
		String midSql = "SELECT d.projectId, currentHeat, area, currentPeriod, realTemperature, avgTemperature, userTemperature, createTime FROM ( SELECT projectId, deviceKey, targetKey, max(createTime) AS time FROM DATA GROUP BY projectId, deviceKey, targetKey, DATE_FORMAT(createTime, '%Y%m%d%H')) d1, DATA d ";
		if (!userId.isEmpty()) {
			midSql += ",control c,project p ";
		}
		midSql += " WHERE d.projectId = d1.projectId AND d.deviceKey = d1.deviceKey AND d.targetKey = d1.targetKey AND d.createTime = d1.time AND d.projectId = '"
				+ projectId + "' ANd createTime>='" + start + " 00:00:00' AND createTime<='" + end + " 23:59:59'";
		if (!userId.isEmpty()) {
			midSql += " AND c.project_id = p.id AND p.id = d.projectId and c.user_id='" + userId + "'";
		}
		String sql = "select sum(" + dataName + "),createTime from (" + midSql + ")a group by createTime";
		List<Object[]> datas = this.getSession().createSQLQuery(sql).list();
		List<Map<String, String>> results = new ArrayList<>();
		for (Object[] data : datas) {
			Map<String, String> result = new HashMap<>();
			result.put("label", data[1].toString());
			result.put("value", data[0].toString());
			results.add(result);
		}
		return results;
	}

	public List<Map<String, String>> dArea(String projectId, String building, String door, String userId,
			String dataName) {
		Criteria c = this.getSession().createCriteria(Data.class);
		ProjectionList projectionList = Projections.projectionList();
		projectionList.add(Projections.property(dataName));
		projectionList.add(Projections.groupProperty("user"));
		projectionList.add(Projections.max("createtime"));
		c.setProjection(projectionList);
		c = c.add(Restrictions.eq("building", building));
		c = c.add(Restrictions.eq("door", door));
		c = c.createCriteria("project");
		c = c.add(Restrictions.eq("id", projectId));
		if (!userId.isEmpty()) {
			c = c.createCriteria("users");
			c = c.add(Restrictions.eq("id", userId));
		}
		List<Object[]> datas = c.list();
		List<Map<String, String>> results = new ArrayList<>();
		for (Object[] data : datas) {
			Map<String, String> result = new HashMap<>();
			result.put("label", data[1].toString());
			result.put("value", data[0].toString());
			results.add(result);
		}
		return results;
	}

	public List<List<Map<String, String>>> temperatureDArea(String projectId, String building, String door,
			String userId) {
		Criteria c = this.getSession().createCriteria(Data.class);
		ProjectionList projectionList = Projections.projectionList();
		projectionList.add(Projections.property("realTemperature"));
		projectionList.add(Projections.property("avgTemperature"));
		projectionList.add(Projections.property("userTemperature"));
		projectionList.add(Projections.groupProperty("user"));
		projectionList.add(Projections.max("createtime"));
		c.setProjection(projectionList);
		c = c.add(Restrictions.eq("building", building));
		c = c.add(Restrictions.eq("door", door));
		c = c.createCriteria("project");
		c = c.add(Restrictions.eq("id", projectId));
		if (!userId.isEmpty()) {
			c = c.createCriteria("users");
			c = c.add(Restrictions.eq("id", userId));
		}
		List<Object[]> datas = c.list();
		List<List<Map<String, String>>> results = new ArrayList<>();
		List<Map<String, String>> reals = new ArrayList<>();
		List<Map<String, String>> avgs = new ArrayList<>();
		List<Map<String, String>> users = new ArrayList<>();
		for (Object[] data : datas) {
			Map<String, String> real = new HashMap<>();
			real.put("label", data[3].toString());
			real.put("value", data[0].toString());
			reals.add(real);
			Map<String, String> avg = new HashMap<>();
			avg.put("label", data[3].toString());
			avg.put("value", data[1].toString());
			avgs.add(real);
			Map<String, String> user = new HashMap<>();
			user.put("label", data[3].toString());
			user.put("value", data[2].toString());
			users.add(real);
		}
		results.add(reals);
		results.add(avgs);
		results.add(users);
		return results;
	}

	public List<Map<String, String>> area(String projectId, String building, String userId, String dataName) {
		Criteria c = this.getSession().createCriteria(Data.class);
		ProjectionList projectionList = Projections.projectionList();
		projectionList.add(Projections.property(dataName));
		projectionList.add(Projections.groupProperty("door"));
		projectionList.add(Projections.max("createtime"));
		c.setProjection(projectionList);
		c = c.add(Restrictions.eq("building", building));
		c = c.createCriteria("project");
		c = c.add(Restrictions.eq("id", projectId));
		if (!userId.isEmpty()) {
			c = c.createCriteria("users");
			c = c.add(Restrictions.eq("id", userId));
		}
		List<Object[]> datas = c.list();
		List<Map<String, String>> results = new ArrayList<>();
		for (Object[] data : datas) {
			Map<String, String> result = new HashMap<>();
			result.put("label", data[1].toString());
			result.put("value", data[0].toString());
			results.add(result);
		}
		return results;
	}

	public Map<String, Object> dashBoard(int startIndex, int length, List<String[]> wherePart,
			List<String[]> orderPart, String userId) {
		String sql = "SELECT projectName, sum(totalHeat) AS heat, sum(area) AS m, sum(totalPeriod) AS period,avg(avgTemperature) AS temperature "
				+ "FROM ( SELECT d1.projectId, d1.projectName, d1.totalHeat, d1.area, d1.totalPeriod,d1.avgTemperature "
				+ "FROM DATA d1, ( SELECT projectId, deviceKey, targetKey, max(createTime) AS time "
				+ "FROM DATA GROUP BY projectId, deviceKey, targetKey ) d2 "
				+ "WHERE d1.createTime = d2.time AND d1.projectId = d2.projectId "
				+ "AND d1.deviceKey = d2.deviceKey AND d1.targetKey = d2.targetKey ) d3";
		if (!userId.equals("")) {
			sql += ", project,user,control where project.id = d3.projectId and control.project_id = project.id and user.id = control.user_id and user.id = '"
					+ userId + "'";
		}
		sql += " GROUP BY projectId order by " + orderPart.get(0)[0] + " " + orderPart.get(0)[1] + " limit "
				+ startIndex + "," + length;
		Map<String, Object> result = new HashMap<>();
		List<Object[]> contents = this.getSession().createSQLQuery(sql).list();
		result.put("recordsTotal", 10);
		result.put("recordsFiltered", 10);
		result.put("data", contents);
		return result;
	}
}
